﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Delonix_Regia_HMS.Classes
{
    class DBManager
    {

        #region Edgar Liu Region

        #region Staff Account
        // List View Of Staff Account Managment Administrator
        public static void ListViewStaffAccountManagementAdministrator(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT Username, Password, StaffName, StaffType FROM Staff s, StaffAccount sa WHERE s.StaffID = sa.Username AND StaffType = 'Administrator';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["Username"].ToString());
                    item.SubItems.Add(dr["Password"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // List View Of Staff Account Managment Manager
        public static void ListViewStaffAccountManagementManager(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT Username, Password, StaffName, StaffType FROM Staff s, StaffAccount sa WHERE s.StaffID = sa.Username AND StaffType = 'Manager';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["Username"].ToString());
                    item.SubItems.Add(dr["Password"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // List View Of Staff Account Managment Receptionist
        public static void ListViewStaffAccountManagementReceptionist(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT Username, Password, StaffName, StaffType FROM Staff s, StaffAccount sa WHERE s.StaffID = sa.Username AND StaffType = 'Receptionist';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["Username"].ToString());
                    item.SubItems.Add(dr["Password"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //List View of Staff Account Management Housekeeping
        public static void ListViewStaffAccountManagementHousekeeper(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT Username, Password, StaffName, StaffType FROM Staff s, StaffAccount sa WHERE s.StaffID = sa.Username AND StaffType = 'Housekeeper';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["Username"].ToString());
                    item.SubItems.Add(dr["Password"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Delete Staff Account Method
        public static void DeleteStaffAccount(int username)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "DELETE FROM StaffAccount WHERE Username = @Username; Update Staff SET StaffType =  'Pending' WHERE StaffID=@Username";
                comm.Parameters.AddWithValue("@Username", username);
                int rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //Modify Staff Account Method
        public static void ModifyStaffAccount(StaffAccount sa, Staff s)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE StaffAccount SET Password = @Password WHERE Username = @Username UPDATE Staff SET StaffType =@StaffType WHERE StaffID = @Username;";
                /*comm.CommandText = "UPDATE Staff SET StaffType =@StaffType WHERE StaffID = @Username;";*/
                comm.Parameters.AddWithValue("@Username", sa.Username);
                comm.Parameters.AddWithValue("@Password", sa.Password);
                comm.Parameters.AddWithValue("@StaffType", s.StaffType);
                int rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Modify Staff Account Method
        /*
        public static void ModifyStaffAccount(int username, string password, string staffType)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE StaffAccount SET Password = @Password WHERE Username = @Username UPDATE Staff SET StaffType =@StaffType WHERE StaffID = @Username;";
                comm.Parameters.AddWithValue("@Username", username);
                comm.Parameters.AddWithValue("@Password", password);
                comm.Parameters.AddWithValue("@StaffType", staffType);
                int rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        */
        //Create Staff Account Method
        public static void CreateStaffAccount(StaffAccount sa, Staff s)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO StaffAccount (Username, Password) VALUES (@Username, @Password) UPDATE Staff SET StaffType = @StaffType where StaffID =@Username";
                /*comm.CommandText = "UPDATE Staff SET StaffType = @StaffType where StaffID =@Username";*/
                comm.Parameters.AddWithValue("@Username", sa.Username);
                comm.Parameters.AddWithValue("@Password", sa.Password);
                comm.Parameters.AddWithValue("@StaffType", s.StaffType);
                comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        //Fill combo box Staff ID for create
        public static void FillComboxBoxStaffID(ComboBox cbBox)
        {
            cbBox.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlDataAdapter da = new SqlDataAdapter("Select StaffID from Staff where StaffType = 'Pending';", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    cbBox.Items.Add(dt.Rows[i]["StaffID"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Customer
        //SearchCustomerByName
        public static void ListViewManageCustomerCustomerInformationByName(ListView customerInformationListView, string firstName)
        {
            customerInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Customer WHERE FirstName LIKE '%' +@FirstName + '%'";
                comm.Parameters.AddWithValue("@FirstName", firstName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem customer = new ListViewItem(dr["NRIC"].ToString());
                    customer.SubItems.Add(dr["FirstName"].ToString());
                    customer.SubItems.Add(dr["LastName"].ToString());
                    customer.SubItems.Add(dr["Occupation"].ToString());
                    customer.SubItems.Add(dr["PlaceOfEmployment"].ToString());
                    customer.SubItems.Add(dr["Nationality"].ToString());
                    customer.SubItems.Add(dr["ContactNumber"].ToString());
                    customer.SubItems.Add(dr["Email"].ToString());
                    customer.SubItems.Add(dr["Address"].ToString());
                    customerInformationListView.Items.Add(customer);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //DisplayAllCustomer
        public static void ListViewManageCustomerDisplayAllCustomerInformation(ListView customerInformationListView)
        {
            customerInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Customer ";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem customer = new ListViewItem(dr["NRIC"].ToString());
                    customer.SubItems.Add(dr["FirstName"].ToString());
                    customer.SubItems.Add(dr["LastName"].ToString());
                    customer.SubItems.Add(dr["Occupation"].ToString());
                    customer.SubItems.Add(dr["PlaceOfEmployment"].ToString());
                    customer.SubItems.Add(dr["Nationality"].ToString());
                    customer.SubItems.Add(dr["ContactNumber"].ToString());
                    customer.SubItems.Add(dr["Email"].ToString());
                    customer.SubItems.Add(dr["Address"].ToString());
                    customerInformationListView.Items.Add(customer);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        //SearchCustomerById
        public static void ListViewManageCustomerInformationByNRIC(ListView customerInformationListView, string NRIC)
        {
            customerInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Customer WHERE NRIC=@NRIC ";
                comm.Parameters.AddWithValue("@NRIC", NRIC);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem customer = new ListViewItem(dr["NRIC"].ToString());
                    customer.SubItems.Add(dr["FirstName"].ToString());
                    customer.SubItems.Add(dr["LastName"].ToString());
                    customer.SubItems.Add(dr["Occupation"].ToString());
                    customer.SubItems.Add(dr["PlaceOfEmployment"].ToString());
                    customer.SubItems.Add(dr["Nationality"].ToString());
                    customer.SubItems.Add(dr["ContactNumber"].ToString());
                    customer.SubItems.Add(dr["Email"].ToString());
                    customer.SubItems.Add(dr["Address"].ToString());
                    customerInformationListView.Items.Add(customer);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Delete Customer Method
        public static void DeleteCustomer(string nric)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "DELETE FROM Customer WHERE NRIC = @NRIC;";
                comm.Parameters.AddWithValue("@NRIC", nric);
                int rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Update Customer Method
        public static void UpdateCustomer(string NRIC, string firstName, string lastName, string occupation, string placeOfEmployment, string nationality, string contactNumber, string email, string address)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Customer SET NRIC = @NRIC, FirstName =@FirstName, LastName =@LastName, Occupation=@Occupation, PlaceOfEmployment=@PlaceOfEmployment, Nationality=@Nationality, ContactNumber=@ContactNumber, Email=@Email, Address=@Address WHERE NRIC=@NRIC;";
                comm.Parameters.AddWithValue("@NRIC", NRIC);
                comm.Parameters.AddWithValue("@FirstName", firstName);
                comm.Parameters.AddWithValue("@LastName", lastName);
                comm.Parameters.AddWithValue("@Occupation", occupation);
                comm.Parameters.AddWithValue("@PlaceOfEmployment", placeOfEmployment);
                comm.Parameters.AddWithValue("@Nationality", nationality);
                comm.Parameters.AddWithValue("@ContactNumber", contactNumber);
                comm.Parameters.AddWithValue("@Email", email);
                comm.Parameters.AddWithValue("@Address", address);
                int rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Create Customer Method
        public static void CreateCustomer(Customer Cus)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO Customer (NRIC, FirstName, LastName, Occupation, PlaceOfEmployment, Nationality, ContactNumber, Email, Address) VALUES (@NRIC, @FirstName, @LastName, @Occupation, @PlaceOfEmployment, @Nationality, @ContactNumber, @Email, @Address) ";
                comm.Parameters.AddWithValue("@NRIC", Cus.Nric);
                comm.Parameters.AddWithValue("@FirstName", Cus.FirstName);
                comm.Parameters.AddWithValue("@LastName", Cus.LastName);
                comm.Parameters.AddWithValue("@Occupation", Cus.Occupation);
                comm.Parameters.AddWithValue("@PlaceOfEmployment", Cus.PlaceOfEmployment);
                comm.Parameters.AddWithValue("@Nationality", Cus.Nationality);
                comm.Parameters.AddWithValue("@ContactNumber", Cus.ContactNumber);
                comm.Parameters.AddWithValue("@Email", Cus.Email);
                comm.Parameters.AddWithValue("@Address", Cus.HomeAddress);
                comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static string RetrieveCustomerNameByStaffID(int StaffID)
        {
            string s = "";
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT StaffName FROM Staff WHERE StaffID=@StaffID ";
                comm.Parameters.AddWithValue("@StaffID", StaffID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = (string)dr["StaffName"];
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return s;
        }

        public static bool CheckCustomerNric(string NRIC)
        {
            bool result = false;
            SqlConnection conn = null;
            int rowsRetrieved = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT NRIC FROM Customer WHERE NRIC=@NRIC ";
                comm.Parameters.AddWithValue("@NRIC", NRIC);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string nric = dr["NRIC"].ToString();
                    rowsRetrieved++;
                }
                //rowsRetrieved = comm.ExecuteNonQuery();
                if (rowsRetrieved > 0)
                {
                    result = true;
                }
                
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #endregion

        #region Shawn Phua Region
        #region RoomAvailability & Booking
        #region check room availability
        //Display Deluxe Smoking Room Availability
        public static void DisplayDeluxeSmokingRoomAvailability(ListView deluxeSmokingRoomAvailablityListView)
        {
            deluxeSmokingRoomAvailablityListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM DeluxeSmokingRoomAvailability";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem smokingRoomAvailability = new ListViewItem(dr["Date"].ToString());
                    smokingRoomAvailability.SubItems.Add(dr["NumberOfRoomsAvailable"].ToString());
                    deluxeSmokingRoomAvailablityListView.Items.Add(smokingRoomAvailability);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Deluxe Non Smoking Room Availability
        public static void DisplayDeluxeNonSmokingRoomAvailability(ListView deluxeNonSmokingRoomAvailablityListView)
        {
            deluxeNonSmokingRoomAvailablityListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM DeluxeNonSmokingRoomAvailability";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem nonSmokingRoomAvailability = new ListViewItem(dr["Date"].ToString());
                    nonSmokingRoomAvailability.SubItems.Add(dr["NumberOfRoomsAvailable"].ToString());
                    deluxeNonSmokingRoomAvailablityListView.Items.Add(nonSmokingRoomAvailability);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Superior Smoking Room Availability
        public static void DisplaySuperiorSmokingRoomAvailability(ListView superiorSmokingRoomAvailablityListView)
        {
            superiorSmokingRoomAvailablityListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM SuperiorSmokingRoomAvailability";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem smokingRoomAvailability = new ListViewItem(dr["Date"].ToString());
                    smokingRoomAvailability.SubItems.Add(dr["NumberOfRoomsAvailable"].ToString());
                    superiorSmokingRoomAvailablityListView.Items.Add(smokingRoomAvailability);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Superior Non Smoking Room Availability
        public static void DisplaySuperiorNonSmokingRoomAvailability(ListView superiorNonSmokingRoomAvailablityListView)
        {
            superiorNonSmokingRoomAvailablityListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM SuperiorNonSmokingRoomAvailability";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem nonSmokingRoomAvailability = new ListViewItem(dr["Date"].ToString());
                    nonSmokingRoomAvailability.SubItems.Add(dr["NumberOfRoomsAvailable"].ToString());
                    superiorNonSmokingRoomAvailablityListView.Items.Add(nonSmokingRoomAvailability);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        //Display All Bookings
        public static void DisplayAllBookings(ListView bookingInformationListView)
        {
            bookingInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Booking WHERE BookingStatus = 'Pending';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem bookingInformation = new ListViewItem(dr["BookingID"].ToString());
                    bookingInformation.SubItems.Add(dr["NRIC"].ToString());
                    bookingInformation.SubItems.Add(dr["RoomType"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckOutDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInTime"].ToString());
                    bookingInformation.SubItems.Add(dr["DesiredCheckOutTime"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfAdults"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfChildrens"].ToString());
                    bookingInformation.SubItems.Add(dr["LateCheckOut"].ToString());
                    bookingInformation.SubItems.Add(dr["BookingStatus"].ToString());
                    bookingInformation.SubItems.Add(dr["AdditionalRemarks"].ToString());
                    bookingInformationListView.Items.Add(bookingInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Bookings By Booking Id
        public static void DisplayBookingsByBookingId(ListView bookingInformationListView, int bookingId)
        {
            bookingInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Booking WHERE BookingId = @BookingId AND BookingStatus = 'Pending'";
                comm.Parameters.AddWithValue("@BookingId", bookingId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem bookingInformation = new ListViewItem(dr["BookingID"].ToString());
                    bookingInformation.SubItems.Add(dr["NRIC"].ToString());
                    bookingInformation.SubItems.Add(dr["RoomType"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckOutDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInTime"].ToString());
                    bookingInformation.SubItems.Add(dr["DesiredCheckOutTime"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfAdults"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfChildrens"].ToString());
                    bookingInformation.SubItems.Add(dr["LateCheckOut"].ToString());
                    bookingInformation.SubItems.Add(dr["BookingStatus"].ToString());
                    bookingInformation.SubItems.Add(dr["AdditionalRemarks"].ToString());
                    bookingInformationListView.Items.Add(bookingInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Bookings By Customer NRIC
        public static void DisplayBookingsByCustomerNRIC(ListView bookingInformationListView, string customerNric)
        {
            bookingInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Booking WHERE NRIC = @NRIC AND BookingStatus = 'Pending'";
                comm.Parameters.AddWithValue("@NRIC", customerNric);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem bookingInformation = new ListViewItem(dr["BookingID"].ToString());
                    bookingInformation.SubItems.Add(dr["NRIC"].ToString());
                    bookingInformation.SubItems.Add(dr["RoomType"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckOutDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInTime"].ToString());
                    bookingInformation.SubItems.Add(dr["DesiredCheckOutTime"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfAdults"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfChildrens"].ToString());
                    bookingInformation.SubItems.Add(dr["LateCheckOut"].ToString());
                    bookingInformation.SubItems.Add(dr["BookingStatus"].ToString());
                    bookingInformation.SubItems.Add(dr["AdditionalRemarks"].ToString());
                    bookingInformationListView.Items.Add(bookingInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Bookings By Customer First Name
        public static void DisplayBookingsByCustomerFirstName(ListView bookingInformationListView, string customerFirstName)
        {
            bookingInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Booking b, Customer c WHERE b.NRIC = c.NRIC AND c.FirstName = @FirstName AND BookingStatus = 'Pending'";
                comm.Parameters.AddWithValue("@FirstName", customerFirstName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem bookingInformation = new ListViewItem(dr["BookingID"].ToString());
                    bookingInformation.SubItems.Add(dr["NRIC"].ToString());
                    bookingInformation.SubItems.Add(dr["RoomType"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckOutDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInTime"].ToString());
                    bookingInformation.SubItems.Add(dr["DesiredCheckOutTime"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfAdults"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfChildrens"].ToString());
                    bookingInformation.SubItems.Add(dr["LateCheckOut"].ToString());
                    bookingInformation.SubItems.Add(dr["BookingStatus"].ToString());
                    bookingInformation.SubItems.Add(dr["AdditionalRemarks"].ToString());
                    bookingInformationListView.Items.Add(bookingInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Update Booking
        public static bool UpdateBooking(Booking b)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Booking SET NRIC = @NRIC, RoomType = @RoomType, CheckInDate = @CheckInDate, CheckOutDate = @CheckOutDate, CheckInTime = @CheckInTime, DesiredCheckOutTime = @DesiredCheckOutTime, NumberOfAdults = @NumberOfAdults, NumberOfChildrens = @NumberOfChildrens, LateCheckOut = @LateCheckOut, AdditionalRemarks = @AdditionalRemarks, BookingStatus = @BookingStatus  WHERE BookingID = @BookingID ;";
                comm.Parameters.AddWithValue("@BookingID", b.BookingID);
                comm.Parameters.AddWithValue("@NRIC", b.Nric);
                comm.Parameters.AddWithValue("@RoomType", b.RoomType);
                comm.Parameters.AddWithValue("@CheckInDate", b.CheckInDate);
                comm.Parameters.AddWithValue("@CheckOutDate", b.CheckOutDate);
                comm.Parameters.AddWithValue("@CheckInTime", b.CheckInTime);
                comm.Parameters.AddWithValue("@DesiredCheckOutTime", b.CheckOutTime);
                comm.Parameters.AddWithValue("@NumberOfAdults", b.NumberOfAdults);
                comm.Parameters.AddWithValue("@NumberOfChildrens", b.NumberOfChildrens);
                comm.Parameters.AddWithValue("@LateCheckOut", b.LateCheckOut);
                if (string.IsNullOrEmpty(b.AdditionalRemarks))
                {
                    comm.Parameters.AddWithValue("@AdditionalRemarks", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@AdditionalRemarks", b.AdditionalRemarks);
                }
                comm.Parameters.AddWithValue("@BookingStatus", b.BookingStatus);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Delete Booking
        public static bool DeleteBooking(int bookingId)
        {
            bool result = false;
            int rowsDeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "DELETE FROM Booking WHERE BookingID = @BookingID;";
                comm.Parameters.AddWithValue("@BookingID", bookingId);
                rowsDeleted = comm.ExecuteNonQuery();
                if (rowsDeleted > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Create booking
        public static bool CreateBooking(Booking b)
        {
            bool result = false;
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO Booking (NRIC, RoomType, CheckInDate, CheckOutDate, CheckInTime, DesiredCheckOutTime, NumberOfAdults, NumberOfChildrens, LateCheckOut, AdditionalRemarks, BookingStatus) VALUES (@NRIC, @RoomType, @CheckInDate, @CheckOutDate, @CheckInTime, @DesiredCheckOutTime, @NumberOfAdults, @NumberOfChildrens, @LateCheckOut, @AdditionalRemarks, @BookingStatus)";
                comm.Parameters.AddWithValue("@NRIC", b.Nric);
                comm.Parameters.AddWithValue("@RoomType", b.RoomType);
                comm.Parameters.AddWithValue("@CheckInDate", b.CheckInDate);
                comm.Parameters.AddWithValue("@CheckOutDate", b.CheckOutDate);
                comm.Parameters.AddWithValue("@CheckInTime", b.CheckInTime);
                comm.Parameters.AddWithValue("@DesiredCheckOutTime", b.CheckOutTime);
                comm.Parameters.AddWithValue("@NumberOfAdults", b.NumberOfAdults);
                comm.Parameters.AddWithValue("@NumberOfChildrens", b.NumberOfChildrens);
                comm.Parameters.AddWithValue("@LateCheckOut", b.LateCheckOut);
                if (string.IsNullOrEmpty(b.AdditionalRemarks))
                {
                    comm.Parameters.AddWithValue("@AdditionalRemarks", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@AdditionalRemarks", b.AdditionalRemarks);
                }
                comm.Parameters.AddWithValue("@BookingStatus", b.BookingStatus);
                rowsInserted = comm.ExecuteNonQuery();
                if (rowsInserted > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Update Deluxe Non Smoking Room Availability For Delete Booking
        public static bool UpdateRoomAvailabilityForDeleteBooking(DateTime date, string roomType)
        {
            bool result = false;
            int rowsUpdated = 0;
            SqlConnection conn = null;
            if (roomType.Equals("Deluxe Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (roomType.Equals("Deluxe Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (roomType.Equals("Superior Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            } 
            return result;
        }

        //Update Deluxe Non Smoking Room Availability For Update Booking
        public static bool UpdateRoomAvailabilityForUpdateBooking(DateTime date, string initialroomType, string updatedRoomType)
        {
            bool result = false;
            int rowsUpdated = 0;
            SqlConnection conn = null;
            if (initialroomType.Equals("Deluxe Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (initialroomType.Equals("Deluxe Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (initialroomType.Equals("Superior Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (initialroomType.Equals("Superior Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable + 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            if (updatedRoomType.Equals("Deluxe Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (updatedRoomType.Equals("Deluxe Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (updatedRoomType.Equals("Superior Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (updatedRoomType.Equals("Superior Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }

        //Update Deluxe Non Smoking Room Availability For Create Booking
        public static bool UpdateRoomAvailabilityForCreateBooking(DateTime date, string roomType)
        {
            bool result = false;
            int rowsUpdated = 0;
            SqlConnection conn = null;
            if (roomType.Equals("Deluxe Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (roomType.Equals("Deluxe Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE DeluxeSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (roomType.Equals("Superior Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorNonSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "UPDATE SuperiorSmokingRoomAvailability SET NumberOfRoomsAvailable = NumberOfRoomsAvailable - 1 WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    rowsUpdated = comm.ExecuteNonQuery();
                    if (rowsUpdated > 0)
                    {
                        result = true;
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return result;
        }

        //check room availability when creating booking or updating booking
        public static int CheckRoomAvailabilityForCreateBookingAndUpdateBooking(DateTime date, string roomType)
        {
            int numberOfAvailableRoomsLeft = 5;
            SqlConnection conn = null;
            if (roomType.Equals("Deluxe Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "SELECT NumberOfRoomsAvailable FROM DeluxeNonSmokingRoomAvailability WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        numberOfAvailableRoomsLeft = (int)(dr["NumberOfRoomsAvailable"]);
                    }
                    dr.Close();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (roomType.Equals("Deluxe Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "SELECT NumberOfRoomsAvailable FROM DeluxeSmokingRoomAvailability WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        numberOfAvailableRoomsLeft = (int)(dr["NumberOfRoomsAvailable"]);
                    }
                    dr.Close();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (roomType.Equals("Superior Non Smoking Room"))
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "SELECT NumberOfRoomsAvailable FROM SuperiorNonSmokingRoomAvailability WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        numberOfAvailableRoomsLeft = (int)(dr["NumberOfRoomsAvailable"]);
                    }
                    dr.Close();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                try
                {
                    conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    conn.Open();
                    comm.CommandText = "SELECT NumberOfRoomsAvailable FROM SuperiorSmokingRoomAvailability WHERE Date = @Date ;";
                    comm.Parameters.AddWithValue("@Date", date);
                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        numberOfAvailableRoomsLeft = (int)(dr["NumberOfRoomsAvailable"]);
                    }
                    dr.Close();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return numberOfAvailableRoomsLeft;
        }
        #endregion

        #region CheckIn
        //Display All CheckIn
        public static void DisplayAllCheckIn(ListView checkInInformationListView)
        {
            checkInInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT ci.* FROM CheckIn ci, Booking b WHERE ci.BookingID = b.BookingID AND b.BookingStatus = 'Checked In';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkInInformation = new ListViewItem(dr["CheckInID"].ToString());
                    checkInInformation.SubItems.Add(dr["BookingID"].ToString());
                    checkInInformation.SubItems.Add(dr["RoomID"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInDate"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInTime"].ToString());
                    checkInInformationListView.Items.Add(checkInInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display CheckIn By CheckIn ID
        public static void DisplayAllCheckInByCheckInId(ListView checkInInformationListView, int checkInId)
        {
            checkInInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckIn WHERE CheckInID = @CheckInID";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkInInformation = new ListViewItem(dr["CheckInID"].ToString());
                    checkInInformation.SubItems.Add(dr["BookingID"].ToString());
                    checkInInformation.SubItems.Add(dr["RoomID"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInDate"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInTime"].ToString());
                    checkInInformationListView.Items.Add(checkInInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display CheckIn By Customer NRIC
        public static void DisplayAllCheckInByCustomerNric(ListView checkInInformationListView, string customerNric)
        {
            checkInInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckIn c, Booking b WHERE c.BookingID = b.BookingID and b.NRIC =@NRIC;";
                comm.Parameters.AddWithValue("@NRIC", customerNric);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkInInformation = new ListViewItem(dr["CheckInID"].ToString());
                    checkInInformation.SubItems.Add(dr["BookingID"].ToString());
                    checkInInformation.SubItems.Add(dr["RoomID"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInDate"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInTime"].ToString());
                    checkInInformationListView.Items.Add(checkInInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display CheckIn By Customer First Name
        public static void DisplayAllCheckInByCustomerFirstName(ListView checkInInformationListView, string customerFirstName)
        {
            checkInInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckIn ci, Booking b, Customer c WHERE ci.BookingID = b.BookingID AND b.NRIC = c.NRIC AND b.NRIC = c.NRIC AND c.FirstName = @FirstName;";
                comm.Parameters.AddWithValue("@FirstName", customerFirstName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkInInformation = new ListViewItem(dr["CheckInID"].ToString());
                    checkInInformation.SubItems.Add(dr["BookingID"].ToString());
                    checkInInformation.SubItems.Add(dr["RoomID"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInDate"].ToString());
                    checkInInformation.SubItems.Add(dr["ActualCheckInTime"].ToString());
                    checkInInformationListView.Items.Add(checkInInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Fill combo box Booking ID for Check In Update
        public static void FillComboxBoxBookingID(ComboBox comboBox)
        {
            comboBox.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlDataAdapter dataAdapter = new SqlDataAdapter("Select BookingID from Booking where BookingStatus = 'Pending';", conn);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    comboBox.Items.Add(dataTable.Rows[i]["BookingID"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Bookings By Booking Id
        public static void DisplayBookingsByBookingIdForCheckInUpdate(ListView bookingInformationListView, int bookingId)
        {
            bookingInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Booking WHERE BookingId = @BookingId AND BookingStatus = 'Checked In'";
                comm.Parameters.AddWithValue("@BookingId", bookingId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem bookingInformation = new ListViewItem(dr["BookingID"].ToString());
                    bookingInformation.SubItems.Add(dr["NRIC"].ToString());
                    bookingInformation.SubItems.Add(dr["RoomType"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckOutDate"].ToString());
                    bookingInformation.SubItems.Add(dr["CheckInTime"].ToString());
                    bookingInformation.SubItems.Add(dr["DesiredCheckOutTime"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfAdults"].ToString());
                    bookingInformation.SubItems.Add(dr["NumberOfChildrens"].ToString());
                    bookingInformation.SubItems.Add(dr["LateCheckOut"].ToString());
                    bookingInformation.SubItems.Add(dr["BookingStatus"].ToString());
                    bookingInformation.SubItems.Add(dr["AdditionalRemarks"].ToString());
                    bookingInformationListView.Items.Add(bookingInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Fill combo box RoomID for Check In Update
        public static void FillComboxBoxRoomID(ComboBox comboBox, int bookingId)
        {
            comboBox.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                conn.Open();
                SqlCommand comm = new SqlCommand("Select Distinct r.RoomID from Room r, Booking b where r.RoomType = b.RoomType AND b.BookingID = @BookingID AND  r.RoomStatus = 'Vacant' ;", conn);
                comm.Parameters.AddWithValue("@BookingID", bookingId);
                comm.ExecuteNonQuery();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    comboBox.Items.Add(dataTable.Rows[i]["RoomID"]);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Update CheckIn Details
        public static bool UpdateCheckIn(CheckIn checkIn)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE CheckIn SET BookingID = @BookingID, RoomID = @RoomID, ActualCheckInDate = @ActualCheckInDate, ActualCheckInTime = @ActualCheckInTime WHERE CheckInID = @CheckInID ;";
                comm.Parameters.AddWithValue("@CheckInID", checkIn.CheckInId);
                comm.Parameters.AddWithValue("@BookingID", checkIn.BookingId);
                comm.Parameters.AddWithValue("@RoomID", checkIn.RoomId);
                comm.Parameters.AddWithValue("@ActualCheckInDate", checkIn.ActualCheckInDate);
                comm.Parameters.AddWithValue("@ActualCheckInTime", checkIn.ActualCheckInTime);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Customer CheckIn
        public static bool CustomerCheckIn(CheckIn cI)
        {
            bool result = false;
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO CheckIn (BookingID, RoomID, ActualCheckInDate, ActualCheckInTime) VALUES (@BookingID, @RoomID, @ActualCheckInDate, @ActualCheckInTime);";
                comm.Parameters.AddWithValue("@BookingID", cI.BookingId);
                comm.Parameters.AddWithValue("@RoomID", cI.RoomId);
                comm.Parameters.AddWithValue("@ActualCheckInDate", cI.ActualCheckInDate);
                comm.Parameters.AddWithValue("@ActualCheckInTime", cI.ActualCheckInTime);
                rowsInserted = comm.ExecuteNonQuery();
                if (rowsInserted > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Update Room Stauts for check in update
        public static bool UpdateRoomStatusCheckInRoomIdUpdate(int initialRoomId, int updatedRoomId)
        {
            bool result = false;
            int rowsUpdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Room SET RoomStatus = 'Occupied' WHERE RoomID = @RoomID; UPDATE Room SET RoomStatus = 'Vacant' WHERE RoomId = @RoomID1";
                comm.Parameters.AddWithValue("@RoomID", updatedRoomId);
                comm.Parameters.AddWithValue("@RoomID1", initialRoomId);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Update Room Status for Check In
        public static bool UpdateRoomStatusCheckIn(int roomId)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Room SET RoomStatus = 'Occupied' WHERE RoomID = @RoomID ;";
                comm.Parameters.AddWithValue("@RoomID", roomId);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Update Booking Status for Check In
        public static bool UpdateBookingStatusCheckIn(int bookingId)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Booking SET BookingStatus = 'Checked In' WHERE  BookingID = @BookingID;";
                comm.Parameters.AddWithValue("@BookingID", bookingId);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region Check Out
        //Display All Check Out
        public static void DisplayAllCheckOut(ListView checkOutInformationListView)
        {
            checkOutInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckOutInvoice";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkOutInformation = new ListViewItem(dr["CheckOutInvoiceID"].ToString());
                    checkOutInformation.SubItems.Add(dr["CheckInID"].ToString());
                    checkOutInformation.SubItems.Add(dr["NRIC"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutDate"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutTime"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalDaysStay"].ToString());
                    checkOutInformation.SubItems.Add(dr["AdditionalCost"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalRoomPrice"].ToString());
                    checkOutInformation.SubItems.Add(dr["GrandTotal"].ToString());
                    checkOutInformationListView.Items.Add(checkOutInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display CheckIn By Check Out Id
        public static void DisplayAllCheckOutByCheckOutId(ListView checkInOutformationListView, int checkOutId)
        {
            checkInOutformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckOutInvoice WHERE CheckOutInvoiceID = @CheckOutInvoiceID";
                comm.Parameters.AddWithValue("@CheckOutInvoiceID", checkOutId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkOutInformation = new ListViewItem(dr["CheckOutInvoiceID"].ToString());
                    checkOutInformation.SubItems.Add(dr["CheckInID"].ToString());
                    checkOutInformation.SubItems.Add(dr["NRIC"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutDate"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutTime"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalDaysStay"].ToString());
                    checkOutInformation.SubItems.Add(dr["AdditionalCost"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalRoomPrice"].ToString());
                    checkOutInformation.SubItems.Add(dr["GrandTotal"].ToString());
                    checkInOutformationListView.Items.Add(checkOutInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Check Out By Check In Id
        public static void DisplayAllCheckOutByCheckInId(ListView checkOutInformationListView, int checkInId)
        {
            checkOutInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckOutInvoice WHERE CheckInID = @CheckInID;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkOutInformation = new ListViewItem(dr["CheckOutInvoiceID"].ToString());
                    checkOutInformation.SubItems.Add(dr["CheckInID"].ToString());
                    checkOutInformation.SubItems.Add(dr["NRIC"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutDate"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutTime"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalDaysStay"].ToString());
                    checkOutInformation.SubItems.Add(dr["AdditionalCost"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalRoomPrice"].ToString());
                    checkOutInformation.SubItems.Add(dr["GrandTotal"].ToString());
                    checkOutInformationListView.Items.Add(checkOutInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display CheckOut By Customer First Name
        public static void DisplayAllCheckOutByCustomerFirstName(ListView checkOutInformationListView, string customerFirstName)
        {
            checkOutInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckOutInvoice coi, Customer c WHERE  coi.NRIC = c.NRIC AND c.FirstName = @FirstName;";
                comm.Parameters.AddWithValue("@FirstName", customerFirstName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkOutInformation = new ListViewItem(dr["CheckOutInvoiceID"].ToString());
                    checkOutInformation.SubItems.Add(dr["CheckInID"].ToString());
                    checkOutInformation.SubItems.Add(dr["NRIC"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutDate"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutTime"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalDaysStay"].ToString());
                    checkOutInformation.SubItems.Add(dr["AdditionalCost"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalRoomPrice"].ToString());
                    checkOutInformation.SubItems.Add(dr["GrandTotal"].ToString());
                    checkOutInformationListView.Items.Add(checkOutInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display all Check Out
        public static void DisplayAllCheckOutByCustomerNric(ListView checkInOutformationListView, string customerNric)
        {
            checkInOutformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM CheckOutInvoice WHERE NRIC = @NRIC;";
                comm.Parameters.AddWithValue("@NRIC", customerNric);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkOutInformation = new ListViewItem(dr["CheckOutInvoiceID"].ToString());
                    checkOutInformation.SubItems.Add(dr["CheckInID"].ToString());
                    checkOutInformation.SubItems.Add(dr["NRIC"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutDate"].ToString());
                    checkOutInformation.SubItems.Add(dr["ActualCheckOutTime"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalDaysStay"].ToString());
                    checkOutInformation.SubItems.Add(dr["AdditionalCost"].ToString());
                    checkOutInformation.SubItems.Add(dr["TotalRoomPrice"].ToString());
                    checkOutInformation.SubItems.Add(dr["GrandTotal"].ToString());
                    checkInOutformationListView.Items.Add(checkOutInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Items Consume By Check Out Invoice Id
        public static void DisplayAllItemsConsumeByCheckInId(ListView itemConsumeInformationListView, int checkInId)
        {
            itemConsumeInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM ItemInvoice WHERE CheckInID = @CheckInID;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem checkOutInformation = new ListViewItem(dr["ItemName"].ToString());
                    checkOutInformation.SubItems.Add(dr["Quantity"].ToString());
                    checkOutInformation.SubItems.Add(dr["CheckInID"].ToString());
                    itemConsumeInformationListView.Items.Add(checkOutInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Display Item list view
        public static void DisplayAllItems(ListView itemInformationListView)
        {
            itemInformationListView.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Item";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem bookingInformation = new ListViewItem(dr["ItemName"].ToString());
                    bookingInformation.SubItems.Add(dr["UnitCost"].ToString());
                    itemInformationListView.Items.Add(bookingInformation);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Check if the item that the customer have eaten is already inside the database
        public static bool CheckForItemsConsumed(string itemName, int checkInId)
        {
            SqlConnection conn = null;
            bool result = false;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Select * FROM ItemInvoice WHERE ItemName = @ItemName AND CheckInID = @CheckInID;";
                comm.Parameters.AddWithValue("@ItemName", itemName);
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader  dr = comm.ExecuteReader();
                while(dr.Read())
                {
                    result = true;
                }
                conn.Close();
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Add items consumed by customer 
        public static bool CreateItemsConsumed(ItemInvoice i)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsInserted = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO ItemInvoice (ItemName, Quantity, CheckInID) VALUES (@ItemName, @Quantity, @CheckInID);";
                comm.Parameters.AddWithValue("@ItemName", i.ItemName);
                comm.Parameters.AddWithValue("@Quantity", i.Quantity);
                comm.Parameters.AddWithValue("@CheckInID", i.CheckInId);
                rowsInserted = comm.ExecuteNonQuery();
                if (rowsInserted > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //update items consumed
        public static bool UpdateItemsConsumed(ItemInvoice i)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE ItemInvoice SET ItemName = @ItemName, Quantity = @Quantity, CheckInID = @CheckInID WHERE ItemName = @ItemName AND CheckInID = @CheckInID;";
                comm.Parameters.AddWithValue("@ItemName", i.ItemName);
                comm.Parameters.AddWithValue("@Quantity", i.Quantity);
                comm.Parameters.AddWithValue("@CheckInID", i.CheckInId);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Calculate the Total Item Cost
        public static double CalculateTotalItemCost(int checkInId)
        {
            SqlConnection conn = null;
            double totalItemCost = 0.0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Select (i.UnitCost * ii.Quantity) AS TotalItemCost from Item i, ItemInvoice ii WHERE i.ItemName = ii.ItemName AND ii.CheckInID = @CheckInID;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    totalItemCost += Convert.ToDouble(dr["TotalItemCost"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return totalItemCost;
        }

        //customer check out
        public static bool CustomerCheckOut(CheckOut cO)
        {
            bool result = false;
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO CheckOutInvoice (CheckInID, NRIC, ActualCheckOutDate, ActualCheckOutTime, TotalDaysStay, AdditionalCost, TotalRoomPrice, GrandTotal, PaymentMode, CreditCardNumber, CreditCardExpiryDate) VALUES (@CheckInID, @NRIC, @ActualCheckOutDate, @ActualCheckOutTime, @TotalDaysStay, @AdditionalCost, @TotalRoomPrice, @GrandTotal, @PaymentMode, @CreditCardNumber, @CreditCardExpiryDate);";
                comm.Parameters.AddWithValue("@CheckInID", cO.CheckInID);
                comm.Parameters.AddWithValue("@NRIC", cO.Nric);
                comm.Parameters.AddWithValue("@ActualCheckOutDate", cO.ActualCheckOutDate);
                comm.Parameters.AddWithValue("@ActualCheckOutTime", cO.ActualCheckOutTime);
                comm.Parameters.AddWithValue("@TotalDaysStay", cO.TotalDaysStay);
                comm.Parameters.AddWithValue("@AdditionalCost", cO.AdditionalCost);
                comm.Parameters.AddWithValue("@TotalRoomPrice", cO.TotalRoomPrice);
                comm.Parameters.AddWithValue("@GrandTotal", cO.GrandTotal);
                comm.Parameters.AddWithValue("@PaymentMode", cO.PaymentMode);
                if (string.IsNullOrEmpty(cO.CreditCardNumber))
                {
                    comm.Parameters.AddWithValue("@CreditCardNumber", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@CreditCardNumber", cO.CreditCardNumber);
                }
                if (string.IsNullOrEmpty(cO.CreditCardExpiryDate))
                {
                    comm.Parameters.AddWithValue("@CreditCardExpiryDate", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@CreditCardExpiryDate", cO.CreditCardExpiryDate);
                }
                rowsInserted = comm.ExecuteNonQuery();
                if (rowsInserted > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //get customer nric
        public static string GetCustomerNric(int checkInId)
        {
            string customerNric = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT NRIC FROM Booking b, CheckIn c WHERE CheckInID = @CheckInID AND b.BookingID = c.BookingID;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    customerNric = (string)dr["NRIC"];
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return customerNric;
        }

        //get check in date
        public static DateTime GetCheckInDate(int checkInId)
        {
            DateTime checkInDate = DateTime.MinValue;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT ActualCheckInDate FROM CheckIn  WHERE CheckInID = @CheckInID;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    checkInDate = (DateTime)dr["ActualCheckInDate"];
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return checkInDate;
        }

        //calculate total room price
        public static double GetRoomRates(int checkInId)
        {
            double roomRate = 0.0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT RoomRate FROM RoomType rt, Room r, CheckIn c WHERE r.RoomType = rt.RoomType AND r.RoomID = c.RoomID AND c.CheckInID = @CheckInID";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    roomRate = Convert.ToDouble(dr["RoomRate"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return roomRate;

        }

        //update room status customer check out
        public static bool UpdateCustomerCheckOutRoomStatus(int checkInId)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE r SET RoomStatus = 'Vacant' FROM Room r INNER JOIN CheckIn c ON r.RoomID = c.RoomID AND c.CheckInID = @CheckInID;";
                //comm.CommandText = "UPDATE Room r, CheckIn c SET r.RoomStatus = 'Vacant' WHERE r.RoomID = c.RoomID AND c.CheckInID = @CheckInID ;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //Update Booking Status for Check In
        public static bool UpdateBookingStatusCheckOut(int checkInId)
        {
            SqlConnection conn = null;
            bool result = false;
            int rowsUpdated = 0;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE b SET BookingStatus = 'Checked Out' FROM Booking b INNER JOIN CheckIn c ON b.BookingID = c.BookingID AND c.CheckInID = @CheckInID;";
                //comm.CommandText = "UPDATE Booking b, CheckIn c SET b.BookingStatus = 'Checked Out' WHERE b.BookingID = c.BookingID AND c.CheckInID = @CheckInID ;";
                comm.Parameters.AddWithValue("@CheckInID", checkInId);
                rowsUpdated = comm.ExecuteNonQuery();
                if (rowsUpdated > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #endregion

        #region Marcus Chan Region

        #region Duty Allocation
        // Check from staff table where the staff type is a Housekeeper
        public static void ListViewListCheckForHousekeeper(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Insert Into HousekeepingRecords(StaffID, StaffName)SELECT s.StaffID, s.StaffName From Staff s WHERE StaffType = 'Housekeeper' AND NOT EXISTS( Select 1 FROM HousekeepingRecords t2 WHERE t2.StaffID = s.StaffID AND t2.StaffName = s.StaffName); UPDATE HousekeepingRecords Set RoomID = 0 Where RoomID IS NULL; UPDATE HousekeepingRecords Set Duties = 'No Duties' Where DUties IS NULL; SELECT * FROM HousekeepingRecords;";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["RoomID"].ToString());
                    item.SubItems.Add(dr["HousekeepingDate"].ToString());
                    item.SubItems.Add(dr["Duties"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // Get All the Room ID which are vacant and Fill into ComboBox
        public static void FillDropDownListRoomID(ComboBox cbBox)
        {
            cbBox.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlDataAdapter da = new SqlDataAdapter("Select RoomID From Room Where RoomStatus = 'Vacant'", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    cbBox.Items.Add(dt.Rows[i]["RoomID"]);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // List View of all housekeeper
        public static void ListViewListAllHousekeeper(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Select*From HousekeepingRecords;";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["RoomID"].ToString());
                    item.SubItems.Add(dr["HousekeepingDate"].ToString());
                    item.SubItems.Add(dr["Duties"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // List View of all housekeeper with duties
        public static void ListViewListAllHousekeeperWithDuties(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM HousekeepingRecords Where Duties is not NULL AND Duties !='No Duties';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["RoomID"].ToString());
                    item.SubItems.Add(dr["HousekeepingDate"].ToString());
                    item.SubItems.Add(dr["Duties"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // List View of all housekeeper without duties
        public static void ListViewListAllHousekeeperWithoutDuties(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM HousekeepingRecords Where Duties is NULL or Duties ='No Duties';";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["RoomID"].ToString());
                    item.SubItems.Add(dr["HousekeepingDate"].ToString());
                    item.SubItems.Add(dr["Duties"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // Update/Make duties for housekeeper
        public static bool UpdateHouseKeeperDuties(HousekeepingRecords hkr, int staffID)
        {
            bool result = false;
            int rowsAffected = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Update HousekeepingRecords SET RoomID = @RoomID, HousekeepingDate = @HousekeepingDate, Duties = @Duties Where StaffID = @StaffID;";
                comm.Parameters.AddWithValue("@RoomID", hkr.RoomID);
                comm.Parameters.AddWithValue("@HousekeepingDate", hkr.HousekeepingDateTime);
                comm.Parameters.AddWithValue("@Duties", hkr.Duties);
                comm.Parameters.AddWithValue("@StaffID", staffID);
                rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;

        }

        // Update Room Status
        public static bool UpdateRoomStatus(Room r)
        {
            bool result = false;
            int rowsAffected = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Room SET RoomStatus = @RoomStatus WHERE ROOMID = @RoomID";
                comm.Parameters.AddWithValue("@RoomID", r.RoomID);
                comm.Parameters.AddWithValue("@RoomStatus", r.RoomStatus);
                rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;

        }
        #endregion

        #region Staff Detailed Records

        // List View Of Staff Detail Records
        public static void ListViewStaffDetailRecords(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "Select * FROM Staff;";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffDob"].ToString());
                    item.SubItems.Add(dr["StaffBankAccountNumber"].ToString());
                    item.SubItems.Add(dr["StaffHomeAddress"].ToString());
                    item.SubItems.Add(dr["StaffPhoneNumber"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // Example : Before Code Refractoring 
        /*public static bool insertStaff(string staffName, DateTime staffDOB, string staffBankAccountNumber, string staffHomeAddress, int staffHomeNumber, string staffType)
        {
            bool result = false;
            int rowsAffected = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO Staff (StaffName, StaffDob, StaffBankAccountNumber, StaffHomeAddress, StaffPhoneNumber, StaffType) VALUES (@StaffName, @StaffDob, @StaffBankAccountNumber, @StaffHomeAddress, @StaffPhoneNumber, @StaffType)";
                comm.Parameters.AddWithValue("@StaffName", staffName);
                comm.Parameters.AddWithValue("@StaffDob", staffDOB);
                comm.Parameters.AddWithValue("@StaffBankAccountNumber", staffBankAccountNumber);
                comm.Parameters.AddWithValue("@StaffHomeAddress", staffHomeAddress);
                comm.Parameters.AddWithValue("@StaffPhoneNumber", staffHomeNumber);
                comm.Parameters.AddWithValue("@StaffType", staffType);
                rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }*/
        
        // After Code Refractoring
        // Create New Staff
        public static bool insertStaff(Staff s)
        {
            bool result = false;
            int rowsAffected = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "INSERT INTO Staff (StaffName, StaffDob, StaffBankAccountNumber, StaffHomeAddress, StaffPhoneNumber, StaffType) VALUES (@StaffName, @StaffDob, @StaffBankAccountNumber, @StaffHomeAddress, @StaffPhoneNumber, @StaffType)";
                comm.Parameters.AddWithValue("@StaffName", s.StaffName);
                comm.Parameters.AddWithValue("@StaffDob", s.StaffDOB);
                comm.Parameters.AddWithValue("@StaffBankAccountNumber", s.StaffBankAccountNumber);
                comm.Parameters.AddWithValue("@StaffHomeAddress", s.StaffHomeAddress);
                comm.Parameters.AddWithValue("@StaffPhoneNumber", s.StaffHomeNumber);
                comm.Parameters.AddWithValue("@StaffType", s.StaffType);
                rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        

        // Update Staff
        public static bool updateStaff(Staff s, int staffID)
        {
            bool result = false;
            int rowsAffected = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "UPDATE Staff SET StaffBankAccountNumber = @StaffBankAccountNumber, StaffHomeAddress = @StaffHomeAddress, StaffPhoneNumber = @StaffPhoneNumber WHERE StaffID = @StaffID;";
                comm.Parameters.AddWithValue("@StaffBankAccountNumber", s.StaffBankAccountNumber);
                comm.Parameters.AddWithValue("@StaffHomeAddress", s.StaffHomeAddress);
                comm.Parameters.AddWithValue("@StaffPhoneNumber", s.StaffHomeNumber);
                comm.Parameters.AddWithValue("@StaffID", staffID);
                rowsAffected = comm.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    result = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        // Retrieve Staff by ID
        public static void ListViewStaffDetailRecordsByID(ListView lv, int id)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM STAFF WHERE STAFFID = @StaffId;";
                comm.Parameters.AddWithValue("@StaffID", id);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffDob"].ToString());
                    item.SubItems.Add(dr["StaffBankAccountNumber"].ToString());
                    item.SubItems.Add(dr["StaffHomeAddress"].ToString());
                    item.SubItems.Add(dr["StaffPhoneNumber"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // Retrieve Staff by Name Partial
        public static void ListViewStaffDetailRecordsByName(ListView lv, string partialname)
        {
            lv.Items.Clear();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM STAFF WHERE STAFFNAME LIKE '%' +@StaffName + '%';";
                comm.Parameters.AddWithValue("@StaffName", partialname);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    ListViewItem item = new ListViewItem(dr["StaffID"].ToString());
                    item.SubItems.Add(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["StaffDob"].ToString());
                    item.SubItems.Add(dr["StaffBankAccountNumber"].ToString());
                    item.SubItems.Add(dr["StaffHomeAddress"].ToString());
                    item.SubItems.Add(dr["StaffPhoneNumber"].ToString());
                    item.SubItems.Add(dr["StaffType"].ToString());
                    lv.Items.Add(item);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // Delete Staff(?)
        #endregion

        #region Login
        // Initial checking login
        public static int Login(int username, string password)
        {
            int id = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT DISTINCT * From StaffAccount WHERE Username = @Username and Password = @Password;";
                comm.Parameters.AddWithValue("@Username", username);
                comm.Parameters.AddWithValue("@Password", password);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    id = (int)dr["Username"];
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        public static string sType(int id)
        {
            string staffType = "";
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT Distinct StaffID, StaffType FROM Staff WHERE StaffID = @StaffID";
                comm.Parameters.AddWithValue("@StaffID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    staffType = (string)dr["StaffType"];
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return staffType;

        }
        #endregion
        #endregion

        #region Melvyn Ng Region
        private const int ROOMS = 5;

        public static void RetrieveRmStatus(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM Room";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem item = new ListViewItem(dr["RoomID"].ToString());
                    item.SubItems.Add(dr["RoomStatus"].ToString());
                    lv.Items.Add(item);
                }

                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int[] RetrieveGuestsByRmNo(int id)
        {
            int[] guests = { 0, 0 };
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT NumberOfAdults, NumberOfChildrens FROM Booking WHERE BookingID = (SELECT TOP 1 BookingID " +
                    "FROM CheckIn WHERE RoomID = (SELECT DISTINCT RoomID FROM Room WHERE RoomID = @id AND RoomStatus = 'Occupied')" +
                    "ORDER BY bookingID DESC)";
                comm.Parameters.AddWithValue("@id", id);

                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    guests[0] = Convert.ToInt32(dr["NumberOfAdults"]);
                    guests[1] = Convert.ToInt32(dr["NumberOfChildrens"]);
                }
                dr.Close();
                conn.Close();

                return guests;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RetrieveGuestsAllRmsByDate(ListView lv, DateTime date)
        {
            string sqldate = date.Year + "/" + date.Month + "/" + date.Day;
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT RoomID, NumberOfAdults, NumberOfChildrens FROM Booking b, CheckIn " +
                    "WHERE b.BookingID IN (SELECT BookingID FROM CheckIn) AND @date BETWEEN CheckInDate AND CheckOutDate";
                comm.Parameters.AddWithValue("@date", sqldate);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem item = new ListViewItem(dr["RoomID"].ToString());
                    item.SubItems.Add(dr["NumberOfAdults"].ToString());
                    item.SubItems.Add(dr["NumberOfChildrens"].ToString());
                    lv.Items.Add(item);
                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RetrieveDailyRmOcc(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT dns.Date, dns.NumberOfRoomsAvailable, ds.NumberOfRoomsAvailable, " +
                    "sns.NumberOfRoomsAvailable, ss.NumberOfRoomsAvailable FROM DeluxeNonSmokingRoomAvailability dns, " +
                    "DeluxeSmokingRoomAvailability ds, SuperiorNonSmokingRoomAvailability sns, SuperiorSmokingRoomAvailability ss " +
                    "WHERE dns.Date = ds.Date AND dns.Date = sns.Date AND dns.Date = ss.Date";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    DateTime date = Convert.ToDateTime(dr[0].ToString());
                    ListViewItem item = new ListViewItem(date.ToString("dd/MM/yyyy"));
                    int dnsRmOcc = ROOMS - Convert.ToInt32(dr[1]);
                    int dsRmOcc = ROOMS - Convert.ToInt32(dr[2]);
                    int snsRmOcc = ROOMS - Convert.ToInt32(dr[3]);
                    int ssRmOcc = ROOMS - Convert.ToInt32(dr[4]);
                    item.SubItems.Add(dnsRmOcc.ToString());
                    item.SubItems.Add(dsRmOcc.ToString());
                    item.SubItems.Add(snsRmOcc.ToString());
                    item.SubItems.Add(ssRmOcc.ToString());
                    lv.Items.Add(item);
                }

                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RetrieveWeeklyRmOcc(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT COUNT(*), CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, dns.Date) - 6, dns.Date)), 101), " +
                    "CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, dns.Date), dns.Date)), 101), SUM(dns.NumberOfRoomsAvailable), " +
                    "SUM(ds.NumberOfRoomsAvailable), SUM(sns.NumberOfRoomsAvailable), SUM(ss.NumberOfRoomsAvailable) " +
                    "FROM DeluxeNonSmokingRoomAvailability dns, DeluxeSmokingRoomAvailability ds, SuperiorNonSmokingRoomAvailability sns, " +
                    "SuperiorSmokingRoomAvailability ss WHERE dns.Date = ds.Date AND dns.Date = sns.Date AND dns.Date = ss.Date " +
                    "GROUP BY CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, dns.Date) - 6, dns.Date)), 101), " +
                    "CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, dns.Date), dns.Date)), 101); ";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int totalRms = Convert.ToInt32(dr[0]) * ROOMS;
                    ListViewItem item = new ListViewItem(dr[1].ToString() + " - " + dr[2].ToString());
                    int dnsRmOcc = totalRms - Convert.ToInt32(dr[3]);
                    int dsRmOcc = totalRms - Convert.ToInt32(dr[4]);
                    int snsRmOcc = totalRms - Convert.ToInt32(dr[5]);
                    int ssRmOcc = totalRms - Convert.ToInt32(dr[6]);
                    item.SubItems.Add(dnsRmOcc.ToString());
                    item.SubItems.Add(dsRmOcc.ToString());
                    item.SubItems.Add(snsRmOcc.ToString());
                    item.SubItems.Add(ssRmOcc.ToString());
                    lv.Items.Add(item);
                }

                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RetrieveMonthlyRmOcc(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT COUNT(*), MONTH(dns.Date), YEAR(dns.Date), SUM(dns.NumberOfRoomsAvailable), SUM(ds.NumberOfRoomsAvailable), " +
                    "SUM(sns.NumberOfRoomsAvailable), SUM(ss.NumberOfRoomsAvailable) FROM DeluxeNonSmokingRoomAvailability dns, " +
                    "DeluxeSmokingRoomAvailability ds, SuperiorNonSmokingRoomAvailability sns, SuperiorSmokingRoomAvailability ss " +
                    "WHERE dns.Date = ds.Date AND dns.Date = sns.Date AND dns.Date = ss.Date GROUP BY MONTH(dns.Date), YEAR(dns.Date)";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int totalRms = Convert.ToInt32(dr[0]) * ROOMS;
                    string month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Convert.ToInt32(dr[1]));
                    ListViewItem item = new ListViewItem(month + " " + dr[2].ToString());
                    int dnsRmOcc = totalRms - Convert.ToInt32(dr[3]);
                    int dsRmOcc = totalRms - Convert.ToInt32(dr[4]);
                    int snsRmOcc = totalRms - Convert.ToInt32(dr[5]);
                    int ssRmOcc = totalRms - Convert.ToInt32(dr[6]);
                    item.SubItems.Add(dnsRmOcc.ToString());
                    item.SubItems.Add(dsRmOcc.ToString());
                    item.SubItems.Add(snsRmOcc.ToString());
                    item.SubItems.Add(ssRmOcc.ToString());
                    lv.Items.Add(item);
                }

                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RetrieveYearlyRmOcc(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT COUNT(*), YEAR(dns.Date), SUM(dns.NumberOfRoomsAvailable), SUM(ds.NumberOfRoomsAvailable), " +
                    "SUM(sns.NumberOfRoomsAvailable), SUM(ss.NumberOfRoomsAvailable) FROM DeluxeNonSmokingRoomAvailability dns, " +
                    "DeluxeSmokingRoomAvailability ds, SuperiorNonSmokingRoomAvailability sns, SuperiorSmokingRoomAvailability ss " +
                    "WHERE dns.Date = ds.Date AND dns.Date = sns.Date AND dns.Date = ss.Date GROUP BY YEAR(dns.Date)";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int totalRms = Convert.ToInt32(dr[0]) * ROOMS;
                    ListViewItem item = new ListViewItem("Year " + dr[1].ToString());
                    int dnsRmOcc = totalRms - Convert.ToInt32(dr[2]);
                    int dsRmOcc = totalRms - Convert.ToInt32(dr[3]);
                    int snsRmOcc = totalRms - Convert.ToInt32(dr[4]);
                    int ssRmOcc = totalRms - Convert.ToInt32(dr[5]);
                    item.SubItems.Add(dnsRmOcc.ToString());
                    item.SubItems.Add(dsRmOcc.ToString());
                    item.SubItems.Add(snsRmOcc.ToString());
                    item.SubItems.Add(ssRmOcc.ToString());
                    lv.Items.Add(item);
                }

                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void RetrieveHsekpSchedule(ListView lv)
        {
            lv.Items.Clear();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT * FROM HousekeepingRecords;";
                
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ListViewItem item = new ListViewItem(dr["StaffName"].ToString());
                    item.SubItems.Add(dr["Duties"].ToString());
                    lv.Items.Add(item);
                }

                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<HousekeepingRecords> RetrieveDailyHsekp()
        {
            List<HousekeepingRecords> hlist = new List<HousekeepingRecords>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT HousekeepingDate, StaffName, Duties FROM HousekeepingRecords WHERE Duties!='No Duties'";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    HousekeepingRecords h = new HousekeepingRecords();
                    h.HousekeepingDateTime = Convert.ToDateTime(dr[0].ToString());
                    h.StaffName = dr[1].ToString();
                    h.Duties = dr[2].ToString();
                    hlist.Add(h);
                }

                dr.Close();
                conn.Close();

                return hlist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<HousekeepingRecords> RetrieveWeeklyHsekp()
        {
            List<HousekeepingRecords> hlist = new List<HousekeepingRecords>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection("Data Source=.;Initial Catalog=HotelManagementSystem;User ID=user;Password=Password_2013");
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();
                comm.CommandText = "SELECT CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, HousekeepingDate) - 6, HousekeepingDate)), 101), " +
                    "CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, HousekeepingDate), HousekeepingDate)), 101), StaffName, Duties FROM HousekeepingRecords WHERE Duties!='No Duties'";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    HousekeepingRecords h = new HousekeepingRecords();
                    h.WeekStartDate = dr[0].ToString();
                    h.WeekEndDate = dr[1].ToString();
                    h.StaffName = dr[2].ToString();
                    h.Duties = dr[3].ToString();
                    hlist.Add(h);
                }

                dr.Close();
                conn.Close();

                return hlist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
